/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package lcfgly.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;

/**
 * LDMysql
 * @author lcfgly
 * @Date 2015-1-28 13:36:23
 * @version 1.0
 */
public class LDMysql implements LIDatabase{

    //数据源
    private DataSource dataSource;
    
    public LDMysql(DataSource ds) {
        dataSource=ds;
    }

    /**
     * 给PreparedStatement设置参数
     * @param ps PreparedStatement
     * @param args 参数
     * @throws SQLException 
     */
    private void setArgs(PreparedStatement ps,Object... args)throws SQLException{
        if(args != null){
            int i=1;
            for (Object arg : args) {
                ps.setObject(i, arg);
                i++;
            }
        }
    }
    
    /**
     * 获取PreparedStatement
     * @return PreparedStatement
     * @throws LSqlException 获取失败抛出异常
     */
    private PreparedStatement getPreparedStatement(String sql,Object... args)throws LSqlException{
        Connection conn=null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException ex) {
            throw new LSqlException("LDMysql-execSql:获取Connection失败 - "+ex.getSQLState());
        }
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            setArgs(ps, args);
            return ps;
        } catch (SQLException ex) {
            throw new LSqlException("LDMysql-execSql:生成PreparedStatement失败 - "+ex.getSQLState());
        }
    }
    
    /**
     * 抛出sql语句执行错误的异常
     * @param ex
     * @param msg
     * @throws LSqlException 
     */
    private LSqlException throwFuildSql(SQLException ex,String msg){
        return new LSqlException("LDMysql-"+msg+": SQL执行失败 - "+ex.getMessage());
    }
    
    @Override
    public void execSql(String sql, Object... args) throws LSqlException {
        PreparedStatement ps=getPreparedStatement(sql, args);
        try {
            ps.execute();
            ps.close();
        } catch (SQLException ex) {
            throw  throwFuildSql(ex, "execSql");
        }
    }

    @Override
    public int updateSql(String sql, Object... args) throws LSqlException {
        PreparedStatement ps=getPreparedStatement(sql, args);
        try {
            int reuslt =ps.executeUpdate();
            ps.close();
            return reuslt;
        } catch (SQLException ex){
            throw throwFuildSql(ex,"UpdateSql");
        }
    }

    @Override
    public int deleteSql(String sql, Object... args) throws LSqlException {
        PreparedStatement ps=getPreparedStatement(sql, args);
        try {
            int reuslt =ps.executeUpdate();
            ps.close();
            return reuslt;
        } catch (SQLException ex){
            throw throwFuildSql(ex,"DeleteSql");
        }
    }

    @Override
    public int insertBackId(String sql, Object... args) throws LSqlException {
        Connection conn=null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException ex) {
            throw new LSqlException("LDMysql-execSql:获取Connection失败 - "+ex.getSQLState());
        }
        try {
           PreparedStatement ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            setArgs(ps, args);
            ps.execute();
            int result=0;
            ResultSet set=ps.getGeneratedKeys();
            if(set.next()){
                result=set.getInt(1);
            }
            set.close();
            ps.close();
            return result;
        } catch (SQLException ex) {
            throw throwFuildSql(ex, "InsertBackId");
        }
    }

    @Override
    public boolean insert(String sql, Object... args) throws LSqlException {
        PreparedStatement ps=getPreparedStatement(sql, args);
        try {
            int reuslt =ps.executeUpdate();
            ps.close();
            return reuslt>0;
        } catch (SQLException ex){
            throw throwFuildSql(ex,"Insert");
        }
    }

    @Override
    public int count(String sql, Object... args) throws LSqlException {
        String ss = sql.trim().replaceFirst("^(?i)select .*? from (.*) (order by|group by) (.*)", "SELECT count(1) FROM $1");
        PreparedStatement ps=getPreparedStatement(ss, args);
        try {
            ResultSet rs=ps.executeQuery();
            int result=0;
            if(rs.next()){
                result = rs.getInt(1);
            }
            rs.close();
            ps.close();
            return result;
        } catch (SQLException ex) {
            throw throwFuildSql(ex, "Count");
        }
    }

    @Override
    public Map<String,Object> one(String sql, Object... args) throws LSqlException {
        try {
            PreparedStatement ps=getPreparedStatement(sql, args);
            ResultSet rs=ps.executeQuery();
            ResultSetMetaData md=rs.getMetaData();
            if(rs.next()){
                Map<String,Object> result=new HashMap();
                int i=md.getColumnCount();
                for (int j = 0; j < i; j++) {
                    result.put(md.getColumnName(j), rs.getObject(j+1));
                }
                ps.close();
                rs.close();
                return result;
            }else{
                ps.close();
                rs.close();
                throw new LSqlEmptyException();
            }
        } catch (SQLException ex) {
            throw throwFuildSql(ex, "oneRow");
        }
    }

    @Override
    public List<Map<String,Object>> rows(String sql, Object... args) throws LSqlException {
        try {
            PreparedStatement ps=getPreparedStatement(sql, args);
            ResultSet rs=ps.executeQuery();
            ResultSetMetaData md=rs.getMetaData();
            if(rs.next()){
                List<Map<String,Object>> result=new LinkedList();
                int i=md.getColumnCount();
                do {             
                    Map<String,Object> row=new HashMap();
                    for (int j = 0; j < i; j++) {
                        row.put(md.getColumnName(j), rs.getObject(j+1));
                    }
                    result.add(row);
                } while (rs.next());
                ps.close();
                rs.close();
                return result;
            }else{
                ps.close();
                rs.close();
                throw new LSqlEmptyException();
            }
        } catch (SQLException ex) {
            throw throwFuildSql(ex, "Rows");
        }
    }
    
}
